package nie.dao;

import nie.bean.AncientPainting;
import nie.bean.Catalog;
import nie.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class AncientPaintingDao {

  public List<AncientPainting> getAllAncientPaintings() {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    List<AncientPainting> ancientPaintings = new ArrayList<AncientPainting>();
    try {
      // 获得数据的连接
      conn = JDBCUtils.getConnection();
      // 获得Statement对象
      stmt = conn.createStatement();
      // 发送SQL语句
      String sql = "SELECT * FROM t_ancient_painting";
      rs = stmt.executeQuery(sql);
      // 处理结果集
      while (rs.next()) {
        AncientPainting ancientPainting = new AncientPainting();
        ancientPainting.setId(rs.getInt("id"));
        ancientPainting.setName(rs.getString("name"));
        ancientPainting.setPath(rs.getString("path"));
        ancientPainting.setAuthor(rs.getString("author"));
        ancientPainting.setPrice(rs.getFloat("price"));
        ancientPainting.setPress(rs.getString("press"));
        ancientPainting.setCatalogId(rs.getInt("catalog_id"));
        ancientPainting.setCatalogName(rs.getString("catalog_name"));
        ancientPaintings.add(ancientPainting);
      }
      // 关闭资源
      return ancientPaintings;
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.release(rs, stmt, conn);
    }
    return null;
  }

  public ArrayList<AncientPainting> searchAncientPaintings(String name, String catalogName) {
    ArrayList<AncientPainting> ancientPaintings = new ArrayList<AncientPainting>();
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      conn = JDBCUtils.getConnection();
      stmt = conn.createStatement();
      String sql = "select * from t_ancient_painting";
      if ((name != null && name != "") || (catalogName != null && catalogName != "")) {
        sql += " where 1 = 1";
      }
      if (name != null && !name.equals("")) {
        sql += " and name like '%" + name + "%'";
      }
      if (catalogName != null && !catalogName.equals("")) {
        sql += " and catalog_name like '%" + catalogName + "%'";
      }
      rs = stmt.executeQuery(sql);
      while (rs.next()) {
        AncientPainting ancientPainting = new AncientPainting();
        ancientPainting.setId(rs.getInt("id"));
        ancientPainting.setName(rs.getString("name"));
        ancientPainting.setPath(rs.getString("path"));
        ancientPainting.setAuthor(rs.getString("author"));
        ancientPainting.setPrice(rs.getFloat("price"));
        ancientPainting.setPress(rs.getString("press"));
        ancientPainting.setCatalogId(rs.getInt("catalog_id"));
        ancientPainting.setCatalogName(rs.getString("catalog_name"));
        ancientPaintings.add(ancientPainting);
      }
      return ancientPaintings;

    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.release(rs, stmt, conn);
    }
    return null;
  }

  public AncientPainting getAncientPainting(String id) {
    Connection conn;
    Statement stmt;
    ResultSet rs;
    try {
      conn = JDBCUtils.getConnection();
      stmt = conn.createStatement();
      String sql = "SELECT * FROM t_ancient_painting where id='" + id + "'";
      rs = stmt.executeQuery(sql);
      // 处理结果集
      while (rs.next()) {
        AncientPainting ancientPainting = new AncientPainting();
        ancientPainting.setId(rs.getInt("id"));
        ancientPainting.setName(rs.getString("name"));
        ancientPainting.setAuthor(rs.getString("author"));
        ancientPainting.setPath(rs.getString("path"));
        ancientPainting.setPress(rs.getString("press"));
        ancientPainting.setPrice(rs.getFloat("price"));
        ancientPainting.setPath(rs.getString("path"));
        ancientPainting.setCatalogId(rs.getInt("catalog_id"));
        ancientPainting.setCatalogName(rs.getString("catalog_name"));
        return ancientPainting;
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return null;
  }

  // 获取最后一条数据，为了更新图片路径
  public AncientPainting getLastAncientPainting() {
    Connection conn;
    Statement stmt;
    ResultSet rs;
    try {
      conn = JDBCUtils.getConnection();
      stmt = conn.createStatement();
      String sql = "SELECT * FROM t_ancient_painting order by id desc limit 1";
      rs = stmt.executeQuery(sql);
      // 处理结果集
      while (rs.next()) {
        AncientPainting ancientPainting = new AncientPainting();
        ancientPainting.setId(rs.getInt("id"));
        ancientPainting.setName(rs.getString("name"));
        ancientPainting.setAuthor(rs.getString("author"));
        ancientPainting.setPath(rs.getString("path"));
        ancientPainting.setPress(rs.getString("press"));
        ancientPainting.setPrice(rs.getFloat("price"));
        ancientPainting.setPath(rs.getString("path"));
        ancientPainting.setCatalogId(rs.getInt("catalog_id"));
        ancientPainting.setCatalogName(rs.getString("catalog_name"));
        return ancientPainting;
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return null;
  }

  public boolean addAncientPainting(AncientPainting ancientPainting) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    PreparedStatement ps = null;
    ArrayList<AncientPainting> ancientPaintings = new ArrayList<AncientPainting>();
    try {
      conn = JDBCUtils.getConnection();
      String sql = "insert into t_ancient_painting(name,author,press,price,path,catalog_id,catalog_name) values(?,?,?,?,?,?,?)";
      ps = conn.prepareStatement(sql);
      ps.setString(1, ancientPainting.getName());
      ps.setString(2, ancientPainting.getAuthor());
      ps.setString(3, ancientPainting.getPress());
      ps.setDouble(4, ancientPainting.getPrice());
      ps.setString(5, ancientPainting.getPath());
      ps.setInt(6, ancientPainting.getCatalogId());
      ps.setString(7, ancientPainting.getCatalogName());
      int num = ps.executeUpdate();
      return num > 0;
    } catch (Exception e) {
      e.printStackTrace();
    }
    return false;
  }

  public int updateAncientPainting(AncientPainting ancientPainting) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    PreparedStatement ps = null;
    ArrayList<AncientPainting> ancientPaintings = new ArrayList<AncientPainting>();
    try {
      conn = JDBCUtils.getConnection();
      String sql = "update t_ancient_painting set name = ?, path = ?,  author = ?, price = ?, press = ?, catalog_id = ?, catalog_name = ? where id = ?";
      ps = conn.prepareStatement(sql);
      ps.setString(1, ancientPainting.getName());
      ps.setString(2, ancientPainting.getPath());
      ps.setString(3, ancientPainting.getAuthor());
      ps.setDouble(4, ancientPainting.getPrice());
      ps.setString(5, ancientPainting.getPress());
      ps.setInt(6, ancientPainting.getCatalogId());
      ps.setString(7, ancientPainting.getCatalogName());
      ps.setInt(8, ancientPainting.getId());
      return ps.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.release(ps, conn);
    }
    return 0;
  }

  public int updateAncientPaintingCatalog(Catalog catalog) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    PreparedStatement ps = null;
    ArrayList<AncientPainting> ancientPaintings = new ArrayList<AncientPainting>();
    try {
      conn = JDBCUtils.getConnection();
      String sql = "update t_ancient_painting set catalog_name = ? where catalog_id = ?";
      ps = conn.prepareStatement(sql);
      ps.setString(1, catalog.getName());
      ps.setInt(2, catalog.getId());
      return ps.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.release(ps, conn);
    }
    return 0;
  }

  public int getCountByCatalogId(int catalogId) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      // 获得数据的连接
      conn = JDBCUtils.getConnection();
      // 获得Statement对象
      stmt = conn.createStatement();
      // 发送SQL语句
      String sql = "SELECT count(*) as count FROM t_ancient_painting WHERE catalog_id = " + catalogId;
      rs = stmt.executeQuery(sql);
      int count = 0;
      while (rs.next()) {
        // 处理结果集
        count = rs.getInt("count");
      }
      // 处理结果集
      return count;
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.release(rs, stmt, conn);
    }
    return 0;
  }

  public int deleteById(int id) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
      conn = JDBCUtils.getConnection();
      String sql = "delete from t_ancient_painting where id = ?";
      ps = conn.prepareStatement(sql);
      ps.setInt(1, id);
      return ps.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.release(ps, conn);
    }
    return 0;
  }
}
